SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform, and load data. While ETL processing is common in data warehousing (DW) applications, SSIS is by no means limited to just DW; e.g. when you create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created. At a high level, SSIS provides the ability to:
The first version of SSIS was released with SQL Server 2005. SSIS is a replacement for Data Transformation Services (DTS) which was available with SQL Server 7.0 and SQL Server 2000. SSIS builds on the capabilities introduced with DTS.
In this tutorial, we will step through a number of topics that you need to understand in order to successfully build an SSIS package. Our high-level outline is as follows:
Now let’s take an example of importing data from a text file to the SQL Server database using SSIS. Let’s have the step by step process of how to achieve this task using SSIS.
It will open the BIDS as shown in the below figure. This will similar to the Visual Studio IDE where we normally do the startup projects based on our requirements.
Once the BID studio is open, now we need to create a solution based on our requirement. Since we are going to start with the integration services just move on to File -> New Project or Ctrl + Shift + N. It will open a pop up where we need to select Integration Services Project and give the project name as shown in the below screen:
After creating this new project, we can see that by default a new SSIS package is added (Package.dtsx) when you create an Integration Services Project; you can right-click on it and rename it. Now the designer will show the empty workflow tabs as shown below:
Since our task is to load the text file into the database, we need to create a new connection or otherwise, in simple words we need to create a data source. Right-click on the connection manager pane and select "New Flat File Connection" as shown in the below image:
It will open a window as shown in the below image. We need to fill in the details like the path of the text file, and Format of the text file. If the text file first row contains the columns, then we need to select the "Column names in the first data row", checkbox as shown in the below image:
You can see the columns which we are trying to import from the text file by going to the columns tab or the preview tab in the below image. In the preview tab, we can see the data as per our requirement like say a huge amount of data is available in the source like 1 million records. In the preview tab, it shows only 100 records at a time. To see the records from 500 to 600, you need to specify "Data rows to skip" = 500 in the preview tab. So we can see the records from 501 to 600. Click on the ok button to complete this task.
Now we are done with the source section defined, we need to the similar steps to make the destination section. Now right click on the connection manager pane and select "New OLEDB Connection". It will pop up a window to get the connection details as shown below. Fill in all the connection details and test the connection.
After clicking on Ok, we will see the connection details in the connection manager as shown in the below screen.
Now we need to create the data flow task in order to achieve our requirement to import the data from the text file to the database. SSIS uses the workflow tasks in order to process the request as a step by step process. Most of our tasks will be completed here with writing coding to make it done. So in the toolbox at the left pane, click on the "Data Flow Task" item, and drag it to the screen. You’ll see an icon as shown in the below screen. Double-click on that icon to open it.
If you want to enrich your career and become a professional in SQL Server, then visit Tekslate - a global online training platform: "SQL Server Training" This course will help you to achieve excellence in this domain.
Double click on the Data Flow Task, it will redirect to the Data Flow tab where we need to place our logic how the data flows as per our requirement. Since our requirement is to import the text file Drag and drop the "Flat File Source" from the toolbox to the data flow tab screen. Again double click on the Flat File Source task it will pop up a window which has the connection which we set up at the initial stage as shown in the below screen:
If you could notice there is an option "Retain null values from the source as null values in the data flow." This is disabled by default, which means that null values in the source defined will be treated as empty strings. You can click on the Preview button to double-check your structure.
Now, we need to set the destination flow, i.e., till now we read the data and now we need to insert it to the database as per our requirement. Go to the toolbox and click on the data flow destination section and drag and drop the "OLE DB Destination". Now, we need to map the source and the destination in order to tell SSIS how the map works. So, click on the Flat file Source and drag the green arrow to the Ole DB destination as shown in the below screen:
Now click on the destination task to configure it.
Double click on the Destination task to configure it.
Notice that this also allows you to keep nulls. In our example, we’re going to create a new table, but if you have a table already created, you can select it from the table drop-down menu available. I have created a new table in my Database with the below structure before doing the above steps. So, I can see the table name in the list as shown in the below screen:
create table tNewApplication
(
[intApplication.] int identity(1,1),
AppID uniqueidentifier default newid(),
AppName varchar(100),
AppType varchar(100),
AppOwner varchar(100),
AppCompany varchar(100),
AppState varchar(100),
AppCountry varchar(100)
)
Now we need to do the mapping, i.e., we are going to map the columns from the Source (Flat File Schema) to the destination (DB Schema) as shown in the below screen:
Finally, once everything is configured click on the Ok button. You should see the screen similar to the one below.
Press F5 to start the execution of the SSIS package. We should see each step turn green as the data is read and written. And you can see the data that has been imported from the Text file to the Database.
For an in-depth understanding of SQL Server click on
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
